#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive	

${HIVE_HOME} -S -e "
create database if not EXISTS intention_dwb;
use intention_dwb;
drop table if EXISTS dwb_customer_intention;
create table dwb_customer_intention(
id                          INT,
    create_date_time            STRING,
    update_date_time            STRING COMMENT '最后更新时间',
    deleted                     INT COMMENT '是否被删除（禁用）',
    customer_id                 INT COMMENT '所属客户id',
    origin_type                 STRING COMMENT '数据来源',
    itcast_school_id            INT COMMENT '校区Id',
    itcast_subject_id           INT COMMENT '学科Id',
    origin_channel              STRING COMMENT '来源渠道',
    first_customer_clue_id      INT COMMENT '第一条线索id',
    last_customer_clue_id       INT COMMENT '最后一条线索id',
    process_state               STRING COMMENT '处理状态',
    process_time                STRING COMMENT '处理状态变动时间',
    payment_state               STRING COMMENT '支付状态',
    payment_time                STRING COMMENT '支付状态变动时间',
    signup_state                STRING COMMENT '报名状态',
    signup_time                 STRING COMMENT '报名时间',
    tdepart_id          INT COMMENT '直属部门',
    department_name             STRING COMMENT '部门名称',
    itcast_school_name          STRING COMMENT '校区名称',
    itcast_subject_name         STRING COMMENT '学科名称',
    session_id               STRING COMMENT '七陌会话id',
    sid                      STRING COMMENT '访客id',
    status                   STRING COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
    seo_source               STRING COMMENT '搜索来源',
    ip                       STRING COMMENT 'IP地址',
    referrer                 STRING COMMENT '上级来源页面',
    clue_state               STRING COMMENT '线索状态',
    area                     STRING COMMENT '所在区域',
    O2O                         STRING COMMENT 'online_offline标记',
    customer_mark               STRING COMMENT 'new_old_customer标记',
    yearinfo                    STRING COMMENT '年',
    monthinfo                    STRING COMMENT '月',
    dayinfo                    STRING COMMENT '日',
    hourinfo                    STRING COMMENT '小时'
)comment '客户意向表'
partitioned by (dt string)
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES ('orc.compress'='SNAPPY');

drop table if EXISTS dwb_customer_clue;
create table dwb_customer_clue(
customer_relationship_first_id INT COMMENT '第一条客户关系id',
appeal_status                  INT COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
create_date_time         STRING COMMENT '创建时间',
update_date_time         STRING COMMENT '最后更新时间',
customer_id              INT COMMENT '客户id',
session_id               STRING COMMENT '七陌会话id',
sid                      STRING COMMENT '访客id',
status                   STRING COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
seo_source               STRING COMMENT '搜索来源',
area                     STRING COMMENT '区域',
itcast_school_id         INT COMMENT '校区Id',
itcast_school            STRING COMMENT '校区',
itcast_subject_id        INT COMMENT '学科Id',
itcast_subject           STRING COMMENT '学科',
origin_type              STRING COMMENT '数据来源渠道',
valid                    INT COMMENT '该线索是否是网资有效线索',
clue_state               STRING COMMENT '线索状态',
school_name                     STRING COMMENT '校区名称',
subject_name                     STRING COMMENT '学科名称',
O2O                         STRING COMMENT 'online_offline标记',
customer_mark               STRING COMMENT 'new_old_customer标记',
yearinfo                    STRING COMMENT '年',
monthinfo                    STRING COMMENT '月',
dayinfo                    STRING COMMENT '日',
hourinfo                    STRING COMMENT '小时'

)comment '客户线索表'
partitioned by (dt string)
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES ('orc.compress'='SNAPPY');

"